﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;

namespace HealthExaminationSys.DBUtility
{
    /// <summary>
    /// 
    /// </summary>
    public class DbHelper
    {
        protected virtual DbConfig DbConfig { get { return new DbConfig(); } }
        string ConnectionString { get { return DbConfig.ConnString; } }
        DbProviderFactory Provider { get { return DbProviderFactories.GetFactory(DbConfig.ProviderName); } }
        public DbHelper()
        {
        }
        /// <summary>
        /// 测试连接
        /// </summary>
        /// <returns></returns>
        public bool TestConnection()
        {
            bool val = false;
            using (var conn = Provider.CreateConnection())
            {
                try
                {
                    conn.ConnectionString = ConnectionString;
                    conn.Open();
                    val = true;
                    conn.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex);
                    val = false;
                }
                return val;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public int ExecuteSql(string sqlString)
        {
            using (var conn = Provider.CreateConnection())
            {
                conn.ConnectionString = ConnectionString;
                using (var cmd = Provider.CreateCommand())
                {
                    try
                    {
                        if (conn.State != ConnectionState.Open)
                            conn.Open();
                        cmd.Connection = conn;
                        cmd.CommandText = sqlString;
                        return cmd.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
            }
        }
        public int ExecuteSql(string sqlString, params DbParameter[] dbparams)
        {
            using (var conn = Provider.CreateConnection())
            {
                conn.ConnectionString = ConnectionString;
                using (var cmd = Provider.CreateCommand())
                {
                    try
                    {
                        if (conn.State != ConnectionState.Open)
                            conn.Open();
                        cmd.Connection = conn;
                        PrepareCommand(sqlString, dbparams, cmd);
                        var val = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return val;
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
            }
        }
        public int ExecuteSqlTran(List<string> sqlStrList, out string errMsg)
        {
            errMsg = "执行成功！";
            using (var conn = Provider.CreateConnection())
            {
                conn.ConnectionString = ConnectionString;
                using (var cmd = Provider.CreateCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    using (var tran = conn.BeginTransaction())
                    {
                        cmd.Transaction = tran;
                        try
                        {
                            var val = 0;
                            for (int i = 0; i < sqlStrList.Count; i++)
                            {
                                string sql = sqlStrList[i];
                                if (sql.Trim().Length > 1)
                                {
                                    cmd.CommandText = sql;
                                    val += cmd.ExecuteNonQuery();
                                }
                            }
                            tran.Commit();
                            return val;
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                            errMsg = ex.Message + "\t执行失败，已回滚！";
                            return 0;
                        }
                    }
                }
            }
        }
        public int ExecuteSqlTran(List<Tuple<string, DbParameter[]>> sqlList)
        {
            using (var conn = Provider.CreateConnection())
            {
                conn.ConnectionString = ConnectionString;
                using (var cmd = Provider.CreateCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    using (var tran = conn.BeginTransaction())
                    {
                        cmd.Transaction = tran;
                        try
                        {
                            var val = 0;
                            for (int i = 0; i < sqlList.Count; i++)
                            {
                                string sql = sqlList[i].Item1;
                                if (sql.Trim().Length > 1)
                                {
                                    PrepareCommand(sql, sqlList[i].Item2, cmd);
                                    val += cmd.ExecuteNonQuery();
                                }
                            }
                            tran.Commit();
                            return val;
                        }
                        catch
                        {
                            tran.Rollback();
                            return 0;
                        }
                    }
                }
            }
        }
        public object GetSingle(string sqlString, params DbParameter[] dbParams)
        {
            using (var conn = Provider.CreateConnection())
            {
                conn.ConnectionString = ConnectionString;
                using (var cmd = Provider.CreateCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    PrepareCommand(sqlString, dbParams, cmd);
                    try
                    {
                        var val = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        return val;
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
        }
        public DataTable Query(string sqlString, int startRecord, int maxRecords)
        {
            using (var conn = Provider.CreateConnection())
            {
                conn.ConnectionString = ConnectionString;
                using (var cmd = Provider.CreateCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandText = sqlString;
                    using (var adapter = Provider.CreateDataAdapter())
                    {
                        adapter.SelectCommand = cmd;
                        DataTable dt = new DataTable();
                        adapter.Fill(startRecord, maxRecords, dt);
                        return dt;
                    }
                }
            }
        }
        public DataSet Query(string sqlString, params DbParameter[] dbParams)
        {
            using (var conn = Provider.CreateConnection())
            {
                conn.ConnectionString = ConnectionString;
                using (var cmd = Provider.CreateCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    PrepareCommand(sqlString, dbParams, cmd);
                    using (var adapter = Provider.CreateDataAdapter())
                    {
                        adapter.SelectCommand = cmd;
                        var ds = new DataSet();
                        try
                        {
                            adapter.Fill(ds);
                            cmd.Parameters.Clear();
                            return ds;
                        }
                        catch
                        {
                            throw;
                        }
                    }
                }
            }
        }
        private static void PrepareCommand(string sqlString, DbParameter[] dbparams, DbCommand cmd)
        {
            cmd.CommandText = sqlString;
            foreach (var parameter in dbparams)
            {
                if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                    (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                cmd.Parameters.Add(parameter);
            }
        }
    }
}
